"""
This file will read data from 
"""

import MySQLdb
import csv 

"""
database = "isec2014"
user = "root"
password="123"
port=3306
table = "from_migration_aug13"
file_path = "D:\Research\ISEC-2014\data\matrix1_q36.csv"
"""
database = "sangeeta"
user = "sangeetal"
password="sangeetal"
port=3307
table = "from_migration_aug13"
file_path = "E:\sangeeta\matrix1_q36.csv"
#"""


db1= MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
select_cursor = db1.cursor() 
#s2_cursor = db1.cursor() 


fo = open(file_path, 'wb')
filewriter = csv.writer(fo, delimiter=',', quotechar=' ', quoting=csv.QUOTE_MINIMAL)

ini = "select distinct initial from "+table
mig = "select distinct migrated from "+ table

select_cursor.execute(ini)
initial_site = select_cursor.fetchall()

select_cursor.execute(mig)
migrated_site = select_cursor.fetchall()

all_sites =list()
for site in initial_site:
    #print site[0]
    all_sites.append(site[0].strip())
    
for site2 in migrated_site:
    try:
        index=all_sites.index(site2[0].strip())
    
    except Exception,e:
        #print "site=", site2[0]
        all_sites.append(site2[0].strip())
 
for one in all_sites:
     print "site=", one     



first_row = list()
first_row.append(";")
for site in all_sites:
    site =site.replace(".stackexchange","")
    first_row.append(site)

filewriter.writerow(first_row)

for initial in all_sites:
    row = list()
    preprocessed_initial = initial.replace(".stackexchange","")
    row.append(preprocessed_initial)
    for migrated in all_sites:
        query = "select  count(*) as num from "+table +" where initial = '"+ initial +"' and migrated='"+migrated+"'"
        #print "query=",query
        select_cursor.execute(query)
        count_val = select_cursor.fetchall()
        count = count_val[0][0]        
        print "count=",count
        row.append(count)
    filewriter.writerow(row)       
db1.close()  
fo.close()     
